package com.ruoyi.rsikManage.Access;

import org.json.JSONObject;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RestController;
import org.json.JSONArray;

import java.sql.*;

@RestController
public class AccessDatabaseController {

    @GetMapping("/riskmanage/api/access-database")
    public JSONArray getAccessDatabaseInfo() {
        return getAccess();
    }

    private final String dbPath = "\\\\192.168.4.85\\AudioClientLite(XuZhou)\\gb.mdb"; // 使用双反斜杠
    private final String url = "jdbc:ucanaccess://" + dbPath.replace("\\", "/");

    public JSONArray getAccess() {
        try (Connection conn = DriverManager.getConnection(url)) {
            DatabaseMetaData metaData = conn.getMetaData();
            ResultSet tables = metaData.getTables(null, null, "%", new String[]{"TABLE"});

            JSONArray allTablesJson = new JSONArray();

            while (tables.next()) {
                String tableName = tables.getString("TABLE_NAME");
                JSONObject tableJson = new JSONObject();
                tableJson.put("tableName", tableName);

                // 获取表的列信息
                JSONArray columnsJson = new JSONArray();
                ResultSet columns = metaData.getColumns(null, null, tableName, "%");

                while (columns.next()) {
                    String columnName = columns.getString("COLUMN_NAME");
                    columnsJson.put(columnName);
                }
                tableJson.put("columns", columnsJson);

                // 查询并获取表的所有数据
                try (Statement stmt = conn.createStatement();
                     ResultSet rs = stmt.executeQuery("SELECT * FROM " + tableName)) {

                    JSONArray rowsJson = new JSONArray();
                    while (rs.next()) {
                        JSONObject rowJson = new JSONObject();
                        for (int i = 1; i <= columnsJson.length(); i++) {
                            String columnName = columnsJson.getString(i - 1);
                            rowJson.put(columnName, rs.getObject(columnName));
                        }
                        rowsJson.put(rowJson);
                    }
                    tableJson.put("rows", rowsJson);

                    // 打印单个表的信息（可选）
                    System.out.println("Table Name: " + tableName);
                    System.out.println(tableJson.toString(4)); // 格式化输出JSON
                }

                allTablesJson.put(tableJson);
            }

            // 打印所有表的信息（可选）
            System.out.println("All Tables Info:");
            System.out.println(allTablesJson.toString(4)); // 格式化输出JSON

            return allTablesJson;

        } catch (SQLException e) {
            e.printStackTrace();
            throw new RuntimeException("Failed to retrieve database information", e);
        }
    }
}
